﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace ELearning.Common.Extensions
{
    public static class ExcelHelp
    {
        /// <summary>
        /// 报名管理导入的场合，平台行业Id的列号
        /// </summary>
        private const int EXCEL_COL_PLAT_FORM_INDUSTRY = 14;

        //获取列名委托方法
        public delegate string GetColumnName(string columnName);

        #region Excel2007

        /// <summary>
        /// excel文件流转化成datatable
        /// </summary>
        public static DataTable ExcelToTableForXLSX(Stream fileStream, Hashtable ht = null, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = fileStream)
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                    {
                        if (ht != null)
                        {
                            var o = ht[obj.ToString()].ToString();
                            dt.Columns.Add(new DataColumn(o));
                        }
                        else
                        {
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        }
                    }
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    if (sheet.GetRow(i) == null)
                    {
                        continue;
                    }
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 导入用户
        /// </summary>
        public static DataTable ExcelToTableForXLSXWithUser(Stream fileStream, Hashtable ht = null, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = fileStream)
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                dt.Columns.Add("UserId");
                //columns.Add(0);
                for (var i = 1; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                    {
                        if (ht != null)
                        {
                            var o = ht[obj.ToString()].ToString();
                            dt.Columns.Add(new DataColumn(o));
                        }
                        else
                        {
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        }
                    }
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    if (sheet.GetRow(i) == null)
                    {
                        continue;
                    }
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }


        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xlsx)
        /// </summary>
        /// <param name="file"></param>
        /// <param name="haveNote">是否有备注</param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLSX(string file, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    if (sheet.GetRow(i) == null)
                    {
                        continue;
                    }
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xlsx)
        /// </summary>
        /// <param name="file">文件路径</param>
        /// <param name="excelFirstName">excel中第一条数据列名</param>
        /// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
        /// <param name="haveNote">是否有备注</param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLSX(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                noteExist = haveNote;
                if (haveNote)
                {
                    firstRowNum += 1;
                    var obj = GetValueTypeForXLSX(sheet.GetRow(firstRowNum).GetCell(0) as XSSFCell);
                    if (obj != null && !string.IsNullOrEmpty(excelFirstName))
                    {
                        //判断第二行第一列是否包含excel数据第一列列名，如果包含，则表示存在填写须知
                        if (!obj.ToString().Contains(excelFirstName))
                        {
                            noteExist = false;
                            firstRowNum -= 1;
                        }
                    }
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    if (sheet.GetRow(i) == null)
                    {
                        continue;
                    }
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xlsx)包括空行
        /// </summary>
        /// <param name="haveNote">是否有备注</param>
        public static DataTable ExcelToTableWithEmptyRowForXLSX(string file, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);



                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    IRow row = sheet.GetRow(i) as XSSFRow;

                    foreach (var j in columns)
                    {
                        if (row != null)
                        {
                            ICell cell = row.GetCell(j) as XSSFCell;
                            if (cell != null && cell.CellType == CellType.Numeric)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else //其他数字类型
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                            }
                            else
                            {
                                dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        /// <summary>
        /// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
        /// <param name="file">文件路径</param>
        /// <param name="excelFirstName">excel中第一条数据列名</param>
        /// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
        /// <param name="haveNote">是否有备注</param>
        /// </summary>
        public static DataTable ExcelToTableWithEmptyRowForXLSX(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                noteExist = haveNote;
                if (haveNote)
                {
                    firstRowNum += 1;
                    var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
                    if (obj != null && !string.IsNullOrEmpty(excelFirstName))
                    {
                        //判断第二行第一列是否包含excel数据第一列列名，如果包含，则表示存在填写须知
                        if (!obj.ToString().Contains(excelFirstName))
                        {
                            noteExist = false;
                            firstRowNum -= 1;
                        }
                    }
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);



                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    IRow row = sheet.GetRow(i) as XSSFRow;

                    foreach (var j in columns)
                    {
                        if (row != null)
                        {
                            ICell cell = row.GetCell(j) as XSSFCell;
                            if (cell != null && cell.CellType == CellType.Numeric)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else //其他数字类型
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                            }
                            else
                            {
                                dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xlsx),生成excel文件的字节数组
        /// 参考资料：https://www.cnblogs.com/shengwei/p/4478591.html
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileFullPath">文件保存地址</param>
        public static FileStream TableToExcelForXLSX(DataTable dt, string fileFullPath)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet1");
            //表头
            var row = sheet.CreateRow(0);
            //dt.Columns.Remove("SignTime");//去掉signtime字段，留下格式化以后的
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                //列名称,数据库中字段
                var convertColumnName = dt.Columns[i].ColumnName;
                //var convertColumnName = "";
                //switch (columnName)
                //{
                //    case "UserName":
                //        convertColumnName = "用户名";
                //        break;
                //    case "Name":
                //        convertColumnName = "真实姓名";
                //        break;
                //    case "LastCourseTime":
                //        convertColumnName = "最后上课日期";
                //        break;
                //    case "FinishRate":
                //        convertColumnName = "看课完成率";
                //        break;
                //    case "Phone":
                //        convertColumnName = "手机号";
                //        break;
                //    case "CardNo":
                //        convertColumnName = "身份证号";
                //        break;
                //    case "RefereeName":
                //        convertColumnName = "推荐人";
                //        break;
                //    case "SignTimeStr":
                //        convertColumnName = "报名日期";
                //        break;
                //    default:
                //        convertColumnName = "无列名";
                //        break;
                //}
                cell.SetCellValue(convertColumnName);
            }

            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }


            FileStream sw = File.Create(fileFullPath);
            xssfworkbook.Write(sw);
            //sw.Close();

            //转为字节数组
            //var stream = new MemoryStream();
            //xssfworkbook.Write(stream);
            //var buf = stream.ToArray();

            return sw;
        }

        /// <summary>
        /// 将DataTable转化为excel
        /// </summary>
        /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
        /// <param name="getColumnName">获取列名的方法</param>
        /// <returns>excel</returns>
        public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet");
            //表头
            var row = sheet.CreateRow(0);

            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                //列名称,数据库中字段
                var columnName = dt.Columns[i].ColumnName;
                var convertColumnName = getColumnName(columnName);
                cell.SetCellValue(convertColumnName);
            }

            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }

        /// <summary>
        /// 将DataTable转化为excel
        /// </summary>
        /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
        /// <returns>excel</returns>
        public static byte[] GetExcelForXLSX(DataTable dt)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet");
            //表头
            var row = sheet.CreateRow(0);

            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                //列名称,数据库中字段
                var columnName = dt.Columns[i].ColumnName;
                cell.SetCellValue(columnName);
            }

            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }

        /// <summary>
        /// 将DataTable转化为excel(招生计划完成率报表使用)
        /// </summary>
        /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
        /// <param name="getColumnName">获取列名的方法</param>
        /// <returns>excel</returns>
        public static byte[] GetExcelForXlsxForRateReport(DataTable dt, GetColumnName getColumnName, string tableName)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet");
            //表头
            var row = sheet.CreateRow(0);
            //顶级标头
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                cell.SetCellValue(tableName);

            }
            //二级表头
            var sencondRow = sheet.CreateRow(2);
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = sencondRow.CreateCell(i);
                //列名称,数据库中字段
                var columnName = dt.Columns[i].ColumnName;
                var convertColumnName = getColumnName(columnName);
                cell.SetCellValue(convertColumnName);
            }
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 6));  //合并招生完成率中表名
            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 3);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }

        /// <summary>
        /// 将DataTable转化为excel，表头自己填充,招生计划完成率导出使用（未封装）
        /// </summary>
        /// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
        /// <param name="dataStartRow">excel中数据开始的行数（从0开始）</param>
        /// <returns>excel</returns>
        public static byte[] GetExcelForXLSXForRate(DataTable dt, GetColumnName getColumnName)
        {
            var xssfworkbook = new XSSFWorkbook();
            var style = xssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            var sheet = xssfworkbook.CreateSheet("Sheet1");
            //表头
            var row = sheet.CreateRow(0);
            //顶级标头
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);

                cell.SetCellValue("convertColumnName");
                var convertColumnName = "";
                var columnName = dt.Columns[i].ColumnName;
                convertColumnName = getColumnName(columnName);
                cell.SetCellValue(convertColumnName);

            }
            //二级表头
            var rowSencond = sheet.CreateRow(1);
            for (var i = 2; i < dt.Columns.Count; i++)
            {
                var cell = rowSencond.CreateCell(i);
                //列名称,数据库中字段
                if (i % 3 == 2)
                {
                    cell.SetCellValue("完成");
                }
                else if (i % 3 == 0)
                {
                    cell.SetCellValue("目标");
                }
                else
                {
                    cell.SetCellValue("完成率");
                }
            }
            //合并表头
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));  //合并招生完成率中的年份
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1)); //合并招生完成率中的姓名
            var r = 2;   //合并单元格开始所在列
            for (var range = 0; range < 13; range++)   //13是指12个月加上年度合计
            {
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, r, r + 2));
                r += 3;
            }
            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 2);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }


        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {

                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion

        #region Exce97-2003

        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xls)
        /// </summary>
        public static DataTable ExcelToTableForXLS(Stream fileStream, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = fileStream)
            {
                var xssfworkbook = new HSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }


        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xls)
        /// </summary>
        /// <param name="file"></param>
        /// <param name="haveNote"></param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLS(string file, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new HSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xls)
        /// </summary>
        /// <param name="file">文件路径</param>
        /// <param name="excelFirstName">excel中第一条数据列名</param>
        /// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
        /// <param name="haveNote">是否有备注</param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLS(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new HSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                noteExist = haveNote;
                if (haveNote)
                {
                    firstRowNum += 1;
                    var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
                    if (obj != null && !string.IsNullOrEmpty(excelFirstName))
                    {
                        //判断第二行第一列是否包含excel数据第一列列名，如果包含，则表示存在填写须知
                        if (!obj.ToString().Contains(excelFirstName))
                        {
                            noteExist = false;
                            firstRowNum -= 1;
                        }
                    }
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        ///将Excel文件中的数据读出到DataTable中(xls)包括空行
        /// </summary>
        public static DataTable ExcelToTableWithEmptyRowForXLS(string file, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new HSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    IRow row = sheet.GetRow(i) as HSSFRow;
                    foreach (var j in columns)
                    {
                        if (row != null)
                        {
                            ICell cell = row.GetCell(j) as HSSFCell;
                            if (cell != null && cell.CellType == CellType.Numeric)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else //其他数字类型
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                            }
                            else
                            {
                                dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        /// <summary>
        /// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
        /// <param name="file">文件路径</param>
        /// <param name="excelFirstName">excel中第一条数据列名</param>
        /// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
        /// <param name="haveNote">是否有备注</param>
        /// </summary>
        public static DataTable ExcelToTableWithEmptyRowForXLS(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                var xssfworkbook = new HSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表头  判断是否包含备注
                var firstRowNum = sheet.FirstRowNum;
                noteExist = haveNote;
                if (haveNote)
                {
                    firstRowNum += 1;
                    var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
                    if (obj != null && !string.IsNullOrEmpty(excelFirstName))
                    {
                        //判断第二行第一列是否包含excel数据第一列列名，如果包含，则表示存在填写须知
                        if (!obj.ToString().Contains(excelFirstName))
                        {
                            noteExist = false;
                            firstRowNum -= 1;
                        }
                    }
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    IRow row = sheet.GetRow(i) as HSSFRow;
                    foreach (var j in columns)
                    {
                        if (row != null)
                        {
                            ICell cell = row.GetCell(j) as HSSFCell;
                            if (cell != null && cell.CellType == CellType.Numeric)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else //其他数字类型
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                            }
                            else
                            {
                                dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xls)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcelForXLS(DataTable dt, string file)
        {
            var xssfworkbook = new HSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet1");
            //表头
            var row = sheet.CreateRow(0);
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (var fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 获取单元格类型(xls)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLS(HSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {

                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion

        #region  转化实体为dataTable

        /// <summary>
        /// Convert a List{T} to a DataTable.
        /// </summary>
        public static DataTable ToDataTable<T>(this List<T> items, Hashtable ht = null)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in props)
            {
                Type t = GetCoreType(prop.PropertyType);
                if (ht != null)
                {
                    var k = ht[prop.Name];
                    if (k != null)
                        tb.Columns.Add(k.ToString(), t);
                }
                else
                {
                    tb.Columns.Add(prop.Name, t);
                }
            }
            if (ht != null && !tb.Columns.Contains("原因"))
            {
                tb.Columns.Add("原因", "".GetType());
            }

            foreach (T item in items)
            {
                var values = new object[tb.Columns.Count];
                var j = 0;
                for (int i = 0; i < props.Length; i++)
                {
                    if (ht != null)
                    {
                        if (props[i].Name == "Remark")
                        {
                            values[tb.Columns.Count - 1] = props[i].GetValue(item, null);
                            continue;
                        }
                        var k = ht[props[i].Name];
                        if (k != null)
                        {
                            values[j] = props[i].GetValue(item, null);
                            j++;
                        }
                    }
                    else
                    {
                        values[i] = props[i].GetValue(item, null);
                    }
                }

                tb.Rows.Add(values);
            }

            return tb;
        }

        /// <summary>
        /// Return underlying type if type is Nullable otherwise return the type
        /// </summary>
        public static Type GetCoreType(Type t)
        {
            if (t != null && IsNullable(t))
            {
                if (!t.IsValueType)
                {
                    return t;
                }
                else
                {
                    return Nullable.GetUnderlyingType(t);
                }
            }
            else
            {
                return t;
            }
        }

        /// <summary>
        /// Determine of specified type is nullable
        /// </summary>
        public static bool IsNullable(Type t)
        {
            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
        }

        #endregion

        #region datatable to list

        /// <summary>
        /// DataTable转成List
        /// </summary>
        public static List<T> ToDataList<T>(this DataTable dt)
        {
            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                var s = Activator.CreateInstance<T>();
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    switch (info.Name)
                                    {
                                        case "Sex":
                                            var t = Convert.ChangeType(item[i], "".GetType());
                                            v = SexHelper.SexToInt(t.ToString());
                                            break;
                                        case "Birthday":
                                            v = DateTime.ParseExact(item[i].ToString(), "yyyyMMdd", CultureInfo.CurrentCulture);
                                            break;
                                        default:
                                            v = Convert.ChangeType(item[i], info.PropertyType);
                                            break;
                                    }
                                }

                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }

        #endregion

        /// <summary>
        /// 构造插入的数据库实体
        /// </summary>
        public static DataTable GetImportDt<T>(this DataTable dt)
        {
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                var s = Activator.CreateInstance<T>();
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    if (info.Name == "Sex")
                                    {
                                        var t = Convert.ChangeType(item[i], "".GetType());
                                        v = SexHelper.SexToInt(t.ToString());
                                    }
                                    else if (info.Name == "Birthday")
                                    {
                                        v = DateTime.ParseExact(item[i].ToString(), "yyyyMMdd", CultureInfo.CurrentCulture);
                                    }
                                    else
                                        v = Convert.ChangeType(item[i], info.PropertyType);
                                }

                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
                        }
                    }
                }
            }
            return dt;
        }
    }

}

